Imports System.Data
Imports System.Data.SqlClient
Imports CAPA_Utilitarios
Public Class OrdenCompraDatos

    'Se crea una instancia unica para las conexiones
    Dim conex As Conexion = Conexion.getInstance()

    Function obtenerOrdenesCompra() As DataSet
        Dim ds As New DataSet
        Dim con As SqlConnection = conex.obtenerConexion()

        Try
            con.Open()

            Dim query As String = "SELECT dp.DCP_Codigo, p.PROV_DesCorta, " & _
                                  "CONVERT(VARCHAR(15),dp.DCP_Fecha,103) AS Fecha " & _
                                  "FROM Documento_Proveedor dp " & _
                                  "INNER JOIN Proveedor p " & _
                                  "ON p.PROV_Codigo = dp.PROV_Codigo " & _
                                  "LEFT JOIN VW_MostrarAvanceOrdenCompra ma " & _
                                  "ON ma.CIA = dp.CIA AND ma.SUC_Codigo = dp.SUC_Codigo " & _
                                  "AND ma.TDOC_Codigo = dp.TDOC_Codigo AND ma.DCP_Codigo = dp.DCP_Codigo " & _
                                  "WHERE dp.CIA = '01' AND dp.SUC_Codigo = '01' " & _
                                  "AND dp.TDOC_Codigo = 'OC' " & _
                                  "GROUP BY dp.DCP_Codigo, p.PROV_DesCorta, CONVERT(VARCHAR(15),dp.DCP_Fecha,103) " & _
                                  "HAVING COUNT(dp.DCP_Codigo) - " & _
                                  "SUM(CASE WHEN ISNULL(ma.Solicitado,0)>0 then CASE " & _
                                  "WHEN ma.solicitado-ma.atendido>0 then 0 else 1 end else 0 end) > 0"

            Dim adapter As SqlDataAdapter = New SqlDataAdapter(query, con)
            adapter.Fill(ds, "OrdenCompra")
        Catch ex As Exception
            ds = Nothing
        Finally
            con.Close()
        End Try

        Return ds
    End Function

End Class
